﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyXmlTools
{
    public class AsposeExcel
    {
        private string outFileName = "";
        private string fullFilename = "";
        private Workbook book = null;
        private Worksheet sheet = null;
        public AsposeExcel(string outfilename, string tempfilename)//导出构造数
        {
            outFileName = outfilename;
            book = new Workbook();
            //book.Open(tempfilename);这里我们暂时不用模板

        }
        public AsposeExcel(string fullfilename)//导入构造数
        {
            fullFilename = fullfilename;
            book = new Workbook();
            //book.Open(tempfilename);
            sheet = book.Worksheets[0];
        }

        private void AddHeader(DataTable dt)
        {
            Cell cell = null;
            for (int col = 0; col < dt.Columns.Count; col++)
            {
                cell = sheet.Cells[0, col];
                cell.PutValue(dt.Columns[col].ColumnName);
            }
        }

        private void AddBody(DataTable dt, String name)
        {

            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    sheet.Cells[r + 1, c].PutValue(dt.Rows[r][c].ToString());
                }
            }
        }

        private void AddBody(DataTable dt)
        {
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    if (dt.Rows[r][c].ToString().Contains(".jpg"))
                    {
                        string strimg = dt.Rows[r][c].ToString();

                        if (File.Exists(strimg))
                        {
                            FileStream fs = File.OpenRead(strimg);

                            byte[] imagebu = new byte[fs.Length];
                            fs.Read(imagebu, 0, imagebu.Length);
                            fs.Close();
                            System.IO.MemoryStream objImage = new System.IO.MemoryStream(imagebu);
                            //Get the first worksheet pictures collection
                            Aspose.Cells.Drawing.PictureCollection pictures = sheet.Pictures;
                            System.Drawing.Bitmap bmp = new System.Drawing.Bitmap(objImage);

                            int img_height = bmp.Height;
                            int img_width = bmp.Width;
                            if (img_height > 100)
                            {
                                img_height = 300;
                                img_width = 300;


                                System.Drawing.Image.GetThumbnailImageAbort callb = new System.Drawing.Image.GetThumbnailImageAbort(ThumbnailCallback);
                                System.Drawing.Image ImgOut = bmp.GetThumbnailImage(img_width, img_height, callb, IntPtr.Zero);
                                objImage.Close();
                                objImage = new MemoryStream();
                                ImgOut.Save(objImage, System.Drawing.Imaging.ImageFormat.Jpeg);
                                //ImgOut.Save("d:\\MyForm\\" + url.Substring(url.LastIndexOf("/") + 1));
                            }
                            Cells cells = sheet.Cells;
                            cells.SetRowHeightPixel(r + 1, 100);
                            cells.SetColumnWidthPixel(c, 100);
                            //Insert the picture from the stream to B2 cell
                            pictures.Add(r + 1, c, objImage, 30, 30);
                        }

                    }
                    else
                    {
                        sheet.Cells[r + 1, c].PutValue(dt.Rows[r][c].ToString());
                    }
                }
            }
        }

        private bool ThumbnailCallback()
        {
            return false;
        }
        //导出------------下一篇会用到这个方法
        public Boolean DatatableToExcel(DataTable dt)
        {
            Boolean yn = false;
            try
            {

                //sheet.Name= sheetName;
                //AddTitle(title,dt.Columns.Count);
                AddHeader(dt);
                AddBody(dt);
                sheet.AutoFitColumns();
                //sheet.AutoFitRows();
                book.Save(fullFilename);
                yn = true;
                return yn;
            }
            catch (Exception e)
            {
                return yn;
                // throwe;
            }
        }

        public Boolean DatatableToExcel(DataTable dt, String aa)
        {
            Boolean yn = false;
            try
            {

                //sheet.Name= sheetName;
                //AddTitle(title,dt.Columns.Count);
                AddHeader(dt);
                AddBody(dt, aa);
                sheet.AutoFitColumns();
                //sheet.AutoFitRows();
                book.Save(fullFilename);
                yn = true;
                return yn;
            }
            catch (Exception e)
            {
                return yn;
                // throwe;
            }
        }

        public Boolean DataSetToExcel(DataSet ds)
        {
            Boolean yn = false;
            try
            {
                int i = 2;
                foreach (DataTable dt in ds.Tables)
                {
                    sheet = book.Worksheets.Add("sheet" + i.ToString());

                    AddHeader(dt);
                    AddBody(dt);
                    sheet.AutoFitColumns();
                    i++;
                }

                book.Save(outFileName);
                yn = true;
                return yn;
            }
            catch (Exception e)
            {
                return yn;
                // throwe;
            }
        }
        public DataTable ExcelToDatatable()//导入
        {
            Workbook book = new Workbook();
            book.Open(fullFilename);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;
            //获取excel中的数据保存到一个datatable中
            DataTable dt_Import = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, false);
            // dt_Import.
            return dt_Import;
        }
    }
}
